from sqlalchemy import *
from Category import Category
from Spent import Spent
from User import User
from Payment import Payment
from UserSettings import UserSettings
from Local import Local

#change this string to sotisfy your database configuration
dbAddress = 'mysql://root@localhost/daily_spent_prod'
#dbAddress = 'sqlite:///teste.db'

engine = create_engine(dbAddress, echo=True);
metadata = BoundMetaData(engine)
session = create_session(bind_to = engine)

#mappers
user_table = Table('user', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('name', String(60), nullable=False),
                   Column('login', String(20), unique=True, nullable=False),
                   Column('password', String(20), nullable=False),
                   Column('settings_id', Integer, ForeignKey('settings.id'))
                   )

user_settings_table = Table('settings', metadata,
                       Column('id', Integer, primary_key=True),
                       Column('numberOfShownSpent', Integer,
                              nullable=False, default=30),
                       Column('currency', String(5),
                              nullable=False, default='$')
                       )

payment_table = Table('payment', metadata,
                       Column('id', Integer, primary_key=True),
                       Column('name', String(60), unique=True, nullable=False),
                       Column('user_id', Integer, ForeignKey('user.id'), 
		                    nullable=False)
                       )

category_table = Table('category', metadata,
                       Column('id', Integer, primary_key=True),
                       Column('name', String(60), unique=True, nullable=False),
                       Column('user_id', Integer, ForeignKey('user.id'), 
		                    nullable=False)
                       )

spent_table = Table('spent', metadata,
                       Column('id', Integer, primary_key=True),
                       Column('cotegory_id', Integer, ForeignKey('category.id')),
                       Column('payment_id', Integer, ForeignKey('payment.id')),
                       Column('local_id', Integer, ForeignKey('local.id')),
                       Column('user_id', Integer, ForeignKey('user.id'), 
		                    nullable=False),
                       Column('description', String(200)),
                       Column('date', Date),
                       Column('value', Float, nullable=False)
                       )

local_table = Table('local', metadata,
                        Column('id', Integer, primary_key=True),
                        Column('name', String(30), nullable=False),
                        Column('description', String(100), nullable=True),
                        Column('user_id', Integer, ForeignKey('user.id'), 
		                    nullable=False)
                   )

mapper(User, user_table, properties = {'spents': relation(Spent, lazy=True),
                                'categories': relation(Category, lazy=True,
                                                       order_by=category_table.c.name),
                                'payments': relation(Payment, lazy=True,
                                                     order_by=payment_table.c.name),
                                'settings': relation(UserSettings,
                                    lazy=True, cascade="all, delete-orphan"),
                                'locals': relation(Local, lazy=True,
                                                   order_by=local_table.c.name)})

mapper(UserSettings, user_settings_table)
mapper(Local, local_table, properties = {'user':relation(User, lazy=False)})
mapper(Category, category_table, properties = {'user':relation(User, lazy=False)})
mapper(Payment, payment_table, properties = {'user':relation(User, lazy=False)})

mapper(Spent, spent_table, order_by=desc(spent_table.c.date),
                   properties = {'user':relation(User, lazy=False),
                                'category':relation(Category, lazy=False),
                                'payment':relation(Payment, lazy=False),
                                'local':relation(Local, lazy=False)})


def createTables():
    '''
    This function is responsible for standing up our database modeling.
    It must be always executed into run.py module, but the tables will be
    modified only if it doesn't exists.
    '''

    if not user_settings_table.exists():
        user_settings_table.create()

    if not user_table.exists():
        user_table.create()

    if not category_table.exists():
        category_table.create()
        
    if not payment_table.exists():
        payment_table.create()
        
    
    if not local_table.exists():
        local_table.create()

    if not spent_table.exists():
        spent_table.create()

        
if __name__ == "__main__":
    createTables()
